package com.brother.util;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import com.brother.database.MalaysiaAddressDB;
import com.brother.database.NigeriaAddressDB;
import com.brother.model.LatLngPoint;
import com.brother.service.RealDataService;

public class NigeriaAddress {
	private static Logger log = Logger.getLogger(RealDataService.class);

	private static NigeriaAddress instance = null;
	
	private NigeriaAddress(){
			// lng    lat   lng    lat
			// 2.685,4.287,14.676,16.278
			bounds = new ArrayList<LatLngPoint>();
			LatLngPoint p = new LatLngPoint(4.287f, 2.685f);
			bounds.add(p);
			p = new LatLngPoint(4.287f, 14.676f);
			bounds.add(p);
			p = new LatLngPoint(16.278f, 14.676f);
			bounds.add(p);
			p = new LatLngPoint(16.278f, 2.685f);
			bounds.add(p);
	}
	
	public static NigeriaAddress getInstance(){
		if(instance == null){
			instance = new NigeriaAddress();
		}
		return instance;
	}
	
	private List<LatLngPoint> bounds ;
	
	public String getAddressByDB(float lat, float lng){
		if(!this.isIntesect(lat, lng)){
			return  null;
		}
		String sql = "select address from poi_point where (lat between "+(lat-0.0005)+"  and "+(lat+0.0005)+
		") and (lng between "+(lng-0.0005)+" and "+(lng+0.0005)+") ";
		
		ResultSet rs = NigeriaAddressDB.query(sql);
		
		String address = "";
		try {
			while(rs.next()){
				if(rs.getString("address") == null || rs.getString("address").trim().length() == 0){
					continue;
				}
				address = rs.getString("address");
			}
		} catch (SQLException e) {
			log.error("", e);
		}
		
		sql = "select address from road_polyline where" +
		" Intersects(GeomFromText('MULTILINESTRING(("+lng+" "+(lat-0.0001)+", "+lng+" "+(lat+0.0002)+"))'),p_line)=1";
		rs = NigeriaAddressDB.query(sql);
		//System.out.println(sql);
		try {
			while(rs.next()){
				if(rs.getString("address") == null || rs.getString("address").trim().length() == 0){
					continue;
				}
				if(address.length() > 0){
					address = address + ", " +rs.getString("address");
				}else{
					address = rs.getString("address");
				}
				break;
			}
		} catch (SQLException e) {
			log.error("", e);
		}
		
		sql = "select address from `road_region` where" +
		" Within(GeomFromText('point("+lng+" "+lat+"))'),`region`)=1";
		rs = NigeriaAddressDB.query(sql);
//		System.out.println(sql);
		try {
			while(rs.next()){
				if(rs.getString("address") == null || rs.getString("address").trim().length() == 0){
					continue;
				}
				if(address.length() > 0){
					address = address + ", " +rs.getString("address");
				}else{
					address = rs.getString("address");
				}
				break;
			}
		} catch (SQLException e) {
			log.error("", e);
		}
		
		sql = "select address from `govt_area_region` where" +
		" Within(GeomFromText('point("+lng+" "+lat+"))'),`region`)=1";
		rs = NigeriaAddressDB.query(sql);
//		System.out.println(sql);
		try {
			while(rs.next()){
				if(rs.getString("address") == null || rs.getString("address").trim().length() == 0){
					continue;
				}
				if(address.length() > 0){
					address = address + ", " +rs.getString("address");
				}else{
					address = rs.getString("address");
				}
				break;
			}
		} catch (SQLException e) {
			log.error("", e);
		}
		
		sql = "select address from `govt_area_region` where" +
		" Within(GeomFromText('point("+lng+" "+lat+"))'),`region`)=1";
		rs = NigeriaAddressDB.query(sql);
//		System.out.println(sql);
		try {
			while(rs.next()){
				if(rs.getString("address") == null || rs.getString("address").trim().length() == 0){
					continue;
				}
				if(address.length() > 0){
					address = address + ", " +rs.getString("address");
				}else{
					address = rs.getString("address");
				}
				break;
			}
		} catch (SQLException e) {
			log.error("", e);
		}
		sql = "select address from `admin_region` where" +
		" Within(GeomFromText('point("+lng+" "+lat+"))'),`region`)=1";
		rs = NigeriaAddressDB.query(sql);
//		System.out.println(sql);
		try {
			while(rs.next()){
				if(rs.getString("address") == null || rs.getString("address").trim().length() == 0){
					continue;
				}
				if(address.length() > 0){
					address = address + ", " +rs.getString("address");
				}else{
					address = rs.getString("address");
				}
				break;
			}
			if(address.length() == 0){
				address = "Nigegria";
			}else{
				address = address+", Nigegria";
			}
		} catch (SQLException e) {
			log.error("", e);
		}
		NigeriaAddressDB.closeThreadConnection();
		return address;
		
	}
	

	public boolean isIntesect(float lat, float lng){
		return false;
//		return PointIntersect.isInsidePolygon(bounds, new LatLngPoint(lat, lng));
	}
	
}
